#!/bin/sh
#行程表-周维度
table_name=app_trip_data_pwi

create_table_sql=$(cat <<EOF
    DROP TABLE IF EXISTS ${KUDU_DATABASE}.${table_name};
    CREATE TABLE IF NOT EXISTS ${KUDU_DATABASE}.${table_name}(
          tid                              bigint    COMMENT '通讯号',
                      pdt                              int       COMMENT 'yyyyMMdd 数据时间',
                      sharp_step_on_gas_number         int       COMMENT '急踩油门次数',
                      trip_power                       int       COMMENT '行程消耗功率单位:KWH',
                      trip_mileage                     int       COMMENT '根据0200结果精准后的里程单位:M',
                      trip_fuel                        int       COMMENT '根据0200结果精准后的油耗单位:ML',
                      trip_duration                    int       COMMENT '行程时长单位:秒（行程结束时间-开始时间）=引擎工作时长engineWorkTime',
                      max_useful_speed                 int       COMMENT '有效最高速度，单位：KM/H',
                      min_useful_speed                 int       COMMENT '有效最小速度，单位：KM/H',
                      in_gear_coast_mileage            int       COMMENT '在挡滑行里程单位:M',
                      in_gear_coast_duration           int       COMMENT '在挡滑行时长单位：秒',
                      in_gear_coast_number             int       COMMENT '在挡滑行次数',
                      brake_number                     int       COMMENT '制动次数',
                      brake_mileage                    int       COMMENT '制动累计里程(制动总距离)单位:M',
                      brake_duration                   int       COMMENT '制动时长单位:S',
                      vehicle_estimated_load           int       COMMENT '整车估算载荷单位:0.1吨',
                      route_integral_mileage           int       COMMENT '行程内积分里程（行程内车速积分里程）单位:M',
                      route_integral_fuel              int       COMMENT '行程内积分油耗单位:ML',
                      max_speed                        int       COMMENT '最大车速单位:KM/H',
                      min_speed                        int       COMMENT '最小车速单位:KM/H',
                      parking_power_on_duration        int       COMMENT '停车通电时长单位:S',
                      city_driving_mileage             int       COMMENT '城市行驶里程单位:M',
                      city_driving_duration            int       COMMENT '城市行驶时长单位:S',
                      city_driving_fuel                int       COMMENT '城市行驶油量单位:ML',
                      rural_driving_mileage            int       COMMENT '非城市行驶里程单位:M',
                      rural_driving_duration           int       COMMENT '非城市行驶时长单位:S',
                      rural_driving_fuel               int       COMMENT '非城市行驶油量单位:ML',
                      eco_driving_mileage              int       COMMENT '经济行驶里程单位:M',
                      eco_driving_duration             int       COMMENT '"经济行驶时长单位:S',
                      eco_driving_fuel                 int       COMMENT '经济行驶油量单位:ML',
                      up_slope_driving_mileage         int       COMMENT '上坡行驶距离单位:M',
                      up_slope_driving_duration        int       COMMENT '上坡行驶时长单位:S',
                      up_slope_driving_fuel            int       COMMENT '上坡行驶油量单位:ML',
                      down_slope_driving_mileage       int       COMMENT '下坡行驶距离单位:M',
                      down_slope_driving_duration      int       COMMENT '下坡行驶时长单位:S',
                      down_slope_driving_fuel          int       COMMENT '下坡行驶油量单位:ML',
                      level_road_driving_mileage       int       COMMENT '平路行驶距离单位:M',
                      level_road_driving_duration      int       COMMENT '平路行驶时长单位:S',
                      level_road_driving_fuel          int       COMMENT '平路行驶油量单位:ML',
                      vehicle_cold_start_mileage       int       COMMENT '冷车起步累计里程单位:M',
                      vehicle_cold_start_duration      int       COMMENT '冷车起步时长单位:S',
                      vehicle_cold_start_fuel          int       COMMENT '冷车起步耗油量单位:ML',
                      cruise_mileage                   int       COMMENT '巡航里程(定速巡航距离)单位:M',
                      cruise_duration                  int       COMMENT '定速巡航时长单位:S',
                      cruise_fuel                      int       COMMENT '定速巡航油量单位:ML',
                      shift_gears_number               int       COMMENT '换挡次数',
                      sharp_down_speed_duration        int       COMMENT '激烈减速时长单位:S',
                      sharp_down_speed_mileage         int       COMMENT '激烈减速距离单位:M',
                      sharp_down_speed_number          int       COMMENT '激烈减速次数',
                      sharp_up_speed_duration          int       COMMENT '激烈加速时长单位:S',
                      sharp_up_speed_mileage           int       COMMENT '激烈加速距离单位:M',
                      sharp_up_speed_number            int       COMMENT '激烈加速次数',
                      sharp_up_speed_fuel              int       COMMENT '激烈加速油耗单位:ML',
                      sharp_step_on_gas_mileage        int       COMMENT '急踩油门行驶距离单位:M',
                      sharp_step_on_gas_duration       int       COMMENT '急踩油门时长单位:S',
                      sharp_step_on_gas_fuel           int       COMMENT '急踩油门油量单位:ML',
                      vehicle_park_number              int       COMMENT '车辆停车次数',
                      vehicle_start_number             int       COMMENT '车辆启步次数',
                      vehicle_cold_start_number        int       COMMENT '冷车启步次数',
                      charge_fuel                      int       COMMENT '加油数量单位:ML',
                      leak_fuel                        int       COMMENT '漏油数量单位:ML',
                      vehicle_start_duration           int       COMMENT '车辆起步时长单位:S',
                      vehicle_start_fuel               int       COMMENT '车辆起步耗油量单位:ML',
                      gas_stability_number             int       COMMENT '油门稳定性次数',
                      gas_stability_duration           int       COMMENT '油门稳定性时长单位:S',
                      cross_cities                     string    COMMENT '经过城市，逗号分隔',
                      avg_speed                        int       COMMENT '平均速度',
                      exceed_speed_range_mileage       int       COMMENT '超区间车速(>120KM/H)累计里程单位:M',
                      exceed_speed_range_duration      int       COMMENT '超区间车速(>120KM/H)累计时长单位:S',
                      exceed_speed_range_fuel          int       COMMENT '超区间车速(>120KM/H)累计油耗单位:ML',
                      assist_brake_mileage             int       COMMENT '辅助制动里程单位:M',
                      assist_brake_duration            int       COMMENT '辅助制动时长单位:S',
                      fatigue_driving_number           int       COMMENT '疲劳驾驶次数',
                      park_throttle_duration           int       COMMENT '停车轰油门时长单位:S',
                      park_throttle_fuel               int       COMMENT '停车轰油门油耗单位:ML',
                      engine_work_duration             int       COMMENT '发动机工作时长单位:S',
                      driving_handbrake_number         int       COMMENT '行驶过程中手刹使用次数',
                      driving_handbrake_duration       int       COMMENT '行驶过程中手刹使用时长单位:S',
                      driving_handbrake_mileage        int       COMMENT '行驶过程中手刹使用里程单位:M',
                      idle_handbrake_number            int       COMMENT '怠速状态下手刹使用次数',
                      idle_handbrake_duration          int       COMMENT '怠速状态下手刹使用时长单位:S',
                      sharp_turning_number             int       COMMENT '急转弯次数',
                      sharp_turning_mil                int       COMMENT '急转弯行驶距离',
                      fatigue_driving_duration         int       COMMENT '疲劳驾驶时长',
                      ton_km_info                      string    COMMENT '按行程累加的吨/公里详细信息json。样例[{"ton":单位0.1吨,"km":单位米},{},...]',
                      cross_cities_info                string    COMMENT '按行程累加的经过城市详细信息[{"lcid":城市编码,"stime":进入时间到秒的时间戳},...]',
                      wload_type_info                  string    COMMENT '载重里程油耗',
                      run_days                         int       COMMENT '运行天数',
                      update_time                      string    COMMENT '写入时间',
          PRIMARY KEY(tid,pdt)
    )PARTITION BY HASH(tid) PARTITIONS 30,
     RANGE (pdt)(
    PARTITION 20210101 <= VALUES < 20220101,
    PARTITION 20220101 <= VALUES < 20230101
)
    STORED AS KUDU
    TBLPROPERTIES (
        'kudu.master_addresses' = '${KUDU_MASTER}',
        'kudu.num_tablet_replicas'='1'
    );
EOF
)

impala-shell -i ${IMPALA_HOST} -d ${KUDU_DATABASE} -q "$create_table_sql"
if [ $? -ne 0 ] ; then
  echo "${table_name}表创建失败"
else
  echo "${table_name}表创建成功"
fi